﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
    public class WorkflowAccess
    {
        public static AccessControlDS.tblWorkflowDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblWorkflow", cn);
                var tbl = new AccessControlDS.tblWorkflowDataTable();
                try
                {
                    cn.Open();

                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tbl.NewtblWorkflowRow();
                        newRow.pkWorkflow = (int)reader["pkWorkflow"];
                        newRow.fkEvent = (int)reader["fkEvent"];
                        newRow.WorkflowName = (String)reader["WorkflowName"];
                        newRow.WorkflowType = (Byte)reader["WorkflowType"];
                        if (reader["fkTwinWorkflow"] != DBNull.Value) newRow.fkTwinWorkflow = (int)reader["fkTwinWorkflow"];
                        if (reader["WorkflowLearningMode"] != DBNull.Value) newRow.WorkflowLearningMode = (Byte)reader["WorkflowLearningMode"];
                        newRow.Enabled = (Boolean)reader["Enabled"];

                        tbl.AddtblWorkflowRow(newRow);
                    }
                }
         
                finally
                {
                    cn.Close();
                }
                return tbl.Rows.Count != 0 ? tbl : null;
            }
        }

        public static DataTable SelectFull()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblWorkflow inner join tblEvent on pkEvent=fkEvent ORDER BY EventName,WorkflowName", cn);
                var tbl = new DataTable();
                try
                {
                    cn.Open();

                    var adp = new SqlDataAdapter(cmdSelect);
                    adp.Fill(tbl);
                }

                finally
                {
                    cn.Close();
                }
                return tbl;
            }
        }

        public static AccessControlDS.tblWorkflowDataTable SelectByFilter(string filter, AccessControlDS.tblWorkflowDataTable tblWorkflowTable)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblWorkflow WHERE {0}", filter), cn);
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblWorkflowTable.NewtblWorkflowRow();
                        newRow.pkWorkflow = (int)reader["pkWorkflow"];
                        newRow.fkEvent = (int)reader["fkEvent"];
                        newRow.WorkflowName = (String)reader["WorkflowName"];
                        newRow.WorkflowType = (Byte)reader["WorkflowType"];
                        if (reader["fkTwinWorkflow"] != DBNull.Value) newRow.fkTwinWorkflow = (int)reader["fkTwinWorkflow"];
                        if (reader["WorkflowLearningMode"] != DBNull.Value) newRow.WorkflowLearningMode = (Byte)reader["WorkflowLearningMode"];
                        newRow.Enabled = (Boolean)reader["Enabled"];
                        tblWorkflowTable.AddtblWorkflowRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblWorkflowTable.Rows.Count != 0 ? tblWorkflowTable : null;
            }
        }

        public static AccessControlDS.tblWorkflowDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblWorkflow WHERE {0}", filter), cn);
                var tblWorkflowTable = new AccessControlDS.tblWorkflowDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblWorkflowTable.NewtblWorkflowRow();
                        newRow.pkWorkflow = (int)reader["pkWorkflow"];
                        newRow.fkEvent = (int)reader["fkEvent"];
                        newRow.WorkflowName = (String)reader["WorkflowName"];
                        newRow.WorkflowType = (Byte)reader["WorkflowType"];
                        if (reader["fkTwinWorkflow"] != DBNull.Value) newRow.fkTwinWorkflow = (int)reader["fkTwinWorkflow"];
                        if (reader["WorkflowLearningMode"] != DBNull.Value) newRow.WorkflowLearningMode = (Byte)reader["WorkflowLearningMode"];
                        newRow.Enabled = (Boolean)reader["Enabled"];
                        tblWorkflowTable.AddtblWorkflowRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblWorkflowTable.Rows.Count != 0 ? tblWorkflowTable : null;
            }
        }

        public static void Insert(AccessControlDS.tblWorkflowRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spWorkflow_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@fkEvent", row.fkEvent);
                cmdInsert.Parameters.AddWithValue("@WorkflowName", row.WorkflowName);
                cmdInsert.Parameters.AddWithValue("@WorkflowType", row.WorkflowType);
                cmdInsert.Parameters.AddWithValue("@fkTwinWorkflow", row.fkTwinWorkflow);
                cmdInsert.Parameters.AddWithValue("@WorkflowLearningMode", row.WorkflowLearningMode);
                cmdInsert.Parameters.AddWithValue("@Enabled", row.Enabled);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblWorkflowRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spWorkflow_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@pkWorkflow", row.pkWorkflow);
                cmdUpdate.Parameters.AddWithValue("@fkEvent", row.fkEvent);
                cmdUpdate.Parameters.AddWithValue("@WorkflowName", row.WorkflowName);
				cmdUpdate.Parameters.AddWithValue("@WorkflowType", row.WorkflowType);
                cmdUpdate.Parameters.AddWithValue("@fkTwinWorkflow", row.fkTwinWorkflow);
                cmdUpdate.Parameters.AddWithValue("@WorkflowLearningMode", row.WorkflowLearningMode);
                cmdUpdate.Parameters.AddWithValue("@Enabled", row.Enabled);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblWorkflowRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spWorkflow_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkWorkflow", row.pkWorkflow);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
}
